package com.service.database.execute;

import cn.hutool.core.util.ArrayUtil;
import cn.hutool.core.util.StrUtil;
import com.mysql.cj.util.StringUtils;
import com.service.database.model.DataColumn;
import com.service.database.config.DatabaseConfig;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.datasource.DriverManagerDataSource;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;

/**
 * 数据库执行器
 * @author xianggj
 * @Date 2021/6/4 18:52
 **/
public class DatabaseEngine {

    private DatabaseConfig configuration;


    public JdbcTemplate getJdbcTemplate(){
        JdbcTemplate jdbcTemplate = new JdbcTemplate(convert());
        return jdbcTemplate;
    }

    /**
     * 将configuration转换成DriverManagerDatasource
     */
    public  DriverManagerDataSource convert(){
        DriverManagerDataSource driverManagerDataSource =
                new DriverManagerDataSource(configuration.getUrl(), configuration.getUserName(),
                        configuration.getUserPwd());
        driverManagerDataSource.setDriverClassName(configuration.getDriver());
        return driverManagerDataSource;
    }

    /**
     * 执行
     * @param query
     * @return
     * @throws Exception
     */
    public void execute(Query query) throws Exception  {
        validateQuery(query);
        this.configuration = query.getConfiguration();
        JdbcTemplate jdbcTemplate = getJdbcTemplate();
        jdbcTemplate.execute(query.getSql());
    }

    /**
     * 批量插入执行
     * @param query
     * @return
     * @throws Exception
     */
    public void executeBatch(Query query, List<Object[]> objs) throws Exception  {
        validateQuery(query);
        this.configuration = query.getConfiguration();
        JdbcTemplate jdbcTemplate = getJdbcTemplate();
        jdbcTemplate.batchUpdate(query.getSql(), objs);
    }

    /**
     * 批量执行
     * @param query
     * @return
     * @throws Exception
     */
    public void executeBatch(Query query) throws Exception  {
        validateQuery(query);
        this.configuration = query.getConfiguration();
        JdbcTemplate jdbcTemplate = getJdbcTemplate();
        jdbcTemplate.execute(query.getSql());
        for (String sql : query.getBatchSql()) {
            jdbcTemplate.update(sql);
        }
    }

    /**
     * 执行修改
     * @param query
     * @return
     * @throws Exception
     */
    public int executeUpdate(Query query) throws Exception  {
        validateQuery(query);
        this.configuration = query.getConfiguration();
        JdbcTemplate jdbcTemplate = getJdbcTemplate();
        Object[] paramers = query.getParamers();
        int i = 0;
        if (ArrayUtil.isEmpty(paramers)){
            i = jdbcTemplate.update(query.getSql());
        } else {
            i = jdbcTemplate.update(query.getSql(), query.getParamers());
        }
        return i;
    }


    /**
     * 执行查询
     * @param query
     * @return
     * @throws Exception
     */
    public Object executeQuery(Query query) throws Exception {
        validateQuery(query);
        this.configuration = query.getConfiguration();
        JdbcTemplate jdbcTemplate = getJdbcTemplate();
        Object[] paramers = query.getParamers();
        List<Map<String, Object>> res = null;
        if (ArrayUtil.isEmpty(paramers)){
            res = jdbcTemplate.queryForList(query.getSql());
        } else {
            res = jdbcTemplate.queryForList(query.getSql(), query.getParamers());
        }
        query.setRs(res);
        return query.doProcess();
    }
    /**
     * 执行查询
     * @param query
     * @return
     * @throws Exception
     */
    public Object executeQuerySort(Query query) throws Exception {
        validateQuery(query);
        this.configuration = query.getConfiguration();
        JdbcTemplate jdbcTemplate = getJdbcTemplate();
        Object[] paramers = query.getParamers();
        List<Map<String, Object>> res = null;
        ResultSetExtractor<List<Map<String, Object>>> resultSetExtractor =
                new ResultSetExtractor<List<Map<String, Object>>>() {
            @Override
            public List<Map<String, Object>> extractData(ResultSet rs)
                    throws SQLException, DataAccessException {
                List<Map<String, Object>> result = new ArrayList();
                List<DataColumn> cols = getDataColumns(rs);
                query.setCols(cols);
                while (rs.next()) {
                    Map<String, Object> map = new HashMap<>();
                    for (DataColumn dc : cols) {
                        map.put(dc.getColumnName(), rs.getObject(dc.getColumnName()));
                    }
                    result.add(map);
                }
                return result;
            }
        };
        if (ArrayUtil.isEmpty(paramers)){
            res =  jdbcTemplate.query(query.getSql(),resultSetExtractor);
        } else {
            res =  jdbcTemplate.query(query.getSql(), query.getParamers(), resultSetExtractor);
        }
        query.setRs(res);
        return query.doProcess();
    }

    /**
     * 获取列
     * @param rs
     * @return
     * @throws SQLException
     */
    private List<DataColumn> getDataColumns(ResultSet rs) throws SQLException {
        int count = rs.getMetaData().getColumnCount();
        List<DataColumn> cols = new ArrayList<>();
        for (int i = 1; i <= count; i++) {
            DataColumn column = new DataColumn();
            String colName = rs.getMetaData().getColumnName(i);
            String labelName = rs.getMetaData().getColumnLabel(i);
            if (StrUtil.isBlank(labelName)) {
                labelName = colName;
            }
            column.setColumnName(labelName);
            String colType = rs.getMetaData().getColumnTypeName(i);
            column.setDataType(colType);
            cols.add(column);
        }
        return cols;
    }

    /**
     * 校验query查询逻辑对象
     *
     * @param query
     */
    private void validateQuery(Query query) {

        if (query == null) {
            throw new NullPointerException("query不能为空");
        }

        if (query.getConfiguration() == null) {
            throw new NullPointerException("数据连接信息不能为空");
        }

        if (StringUtils.isNullOrEmpty(query.getSql())) {
            throw new NullPointerException("sql 语句不能为空");
        }

    }
}
